---
title: "Estat del Sanejament de les aigües residuals | WHO-UNICEF"
author: "Carles Cayuela Linares"
output:
flexdashboard::flex_dashboard:
orientation: columns
vertical_layout: fill
theme:
version: 4
bg: "#3e3e3e"
fg: "#FDF7F7"
primary: "#101010"
navbar-bg: "#000000"
source_code: embed
---
```{r setup, include=FALSE}
library(flexdashboard)
library(openxlsx)
library(crosstalk)
library(ggplot2)
library(dplyr)
library(plotly)
library(rgdal)
library(sf)
library(leaflet)
library(sp)
library(stringr)
library(DT)
library(tidyverse)
library(data.table)
# DADES:
# Conjunt de dades "WHO-UNICEF"
dades <- read.xlsx("data/dades_who.xlsx", sheet = "Sanitation", startRow = 3)
# Conjunt de dades "THe WORLD BANK"
gdp_countries <- read.xlsx("data/GDP_world.xlsx") #https://data.worldbank.org/indicator/NY.GDP.MKTP.CD?end=2020&start=2020
# Conjunt de dades espacials
world_polygons <- read_sf("world-administrative-boundaries/world-administrative-boundaries.shp")
# NETEJA I PREPARACIÓ DE DADES:
## DADES AMB HISTÒRIC:
dades_sanejament_hist <- dades %>%
select(COUNTRY, ISO3, Year, `%.urban`, `Population.(thousands)`, `At.least.basic._NATIONAL`, `Limited.(shared)_NATIONAL`, `Unimproved_NATIONAL`,
`Open.defecation_NATIONAL`, `Annual.rate.of.change.in.basic_NATIONAL`, `Annual.rate.of.change.in.open.defecation_NATIONAL`, `Safely.managed_NATIONAL`,
`Disposed.in.situ_NATIONAL`, `Emptied.and.treated_NATIONAL`, `Wastewater.treated_NATIONAL`, `Latrines.and.other_NATIONAL`, `Septic.tanks_NATIONAL`,
`Sewer.connections_NATIONAL`, `SDG.region`) %>%
mutate_all(funs(str_replace(., ">99", "99.9"))) %>%
mutate_all(funs(str_replace(., "<1", "0.01"))) %>%
mutate(across(`%.urban`:`Sewer.connections_NATIONAL`, as.numeric)) %>%
mutate(across(`%.urban`:`Sewer.connections_NATIONAL`, round, 2)) %>%
mutate(`Safely.managed_NATIONAL` = replace_na(`Safely.managed_NATIONAL`, 0)) %>%
setNames(., c("Pais", "iso3", "Any", "Percent_urba", "Poblacio_milers", "Sanejament_basic", "Sanejament_limitat", "SanejamentNoMillorat", "Sense_Sanejament",
"Rati_canvi_sanejament_basic", "Rati_canvi_Sense_Sanejament", "Gestionada_correctament", "Abocada sense tractat", "Abocada i Tractada", "Tractada",
"Latrines", "Fosses Sèptiques", "Recollida en col·lectors", "regio_SDG"))
## Afegim el GDP com a indicador
gdp_countries_long <- gdp_countries %>%
select(Country.Code, `2000`, `2001`, `2002`, `2003`, `2004`, `2005`, `2006`, `2007`, `2008`, `2009`, `2010`, `2011`, `2012`, `2013`, `2014`,
`2015`, `2016`, `2017`, `2018`, `2019`, `2020`) %>%
pivot_longer(!Country.Code, names_to = c("Any"), values_to = "GDP")
dades_sanejament_hist <- left_join(dades_sanejament_hist, gdp_countries_long, by = c("iso3" = "Country.Code", "Any" = "Any"))
# dades_sanejament_hist$GDP <- dades_sanejament_hist$GDP/1000000 # Convertir a Milions
dades_sanejament_hist$Any <- as.numeric(dades_sanejament_hist$Any)
## Afegim el continent:
dades_sanejament_hist <- merge(dades_sanejament_hist, world_polygons, by="iso3")
## DADES ANY 2020:
dades_subset_sanejament <- dades %>%
filter(Year == "2020") %>%
select(COUNTRY, ISO3, Year, `%.urban`, `Population.(thousands)`, `At.least.basic._NATIONAL`, `Limited.(shared)_NATIONAL`, `Unimproved_NATIONAL`,
`Open.defecation_NATIONAL`, `Annual.rate.of.change.in.basic_NATIONAL`, `Annual.rate.of.change.in.open.defecation_NATIONAL`, `Safely.managed_NATIONAL`,
`Disposed.in.situ_NATIONAL`, `Emptied.and.treated_NATIONAL`, `Wastewater.treated_NATIONAL`, `Latrines.and.other_NATIONAL`, `Septic.tanks_NATIONAL`,
`Sewer.connections_NATIONAL`) %>%
mutate_all(funs(str_replace(., ">99", "99.9"))) %>%
mutate_all(funs(str_replace(., "<1", "0.01"))) %>%
mutate(across(`%.urban`:`Sewer.connections_NATIONAL`, as.numeric)) %>%
mutate(across(`%.urban`:`Sewer.connections_NATIONAL`, round, 2)) %>%
mutate(`Safely.managed_NATIONAL` = replace_na(`Safely.managed_NATIONAL`, 0)) %>%
setNames(., c("Pais", "iso3", "Any", "Percent_urba", "Poblacio_milers", "Sanejament_basic", "Sanejament_limitat", "SanejamentNoMillorat", "Sense_Sanejament",
"Rati_canvi_sanejament_basic", "Rati_canvi_Sense_Sanejament", "Gestionada_correctament", "Abocada sense tractat", "Abocada i Tractada", "Tractada",
"Latrines", "Fosses Sèptiques", "Recollida en col·lectors"))
# MAPA:
world_polygons <- st_simplify(world_polygons)
dades_map <- merge(world_polygons, dades_subset_sanejament, by = "iso3")
# SHARED DATA:
sd <- SharedData$new(dades_map)
sd_hist <- SharedData$new(dades_sanejament_hist)
# PALETTA DEL MAPA:
pal <- colorNumeric(palette = "RdYlGn", domain = seq(0,100,1) )
# SUNBURST
DF_sunburst <- data.table(continent=dades_map$continent, pais=dades_map$Pais, valor=dades_map$Gestionada_correctament)
## Funció per corregir el format de les dades:
as.sunburstDF <- function(DF, value_column = NULL, add_root = FALSE){
require(data.table)
colNamesDF <- names(DF)
if(is.data.table(DF)){
DT <- copy(DF)
} else {
DT <- data.table(DF, stringsAsFactors = FALSE)
}
if(add_root){
DT[, root := ""]
}
colNamesDT <- names(DT)
hierarchy_columns <- setdiff(colNamesDT, value_column)
DT[, (hierarchy_columns) := lapply(.SD, as.factor), .SDcols = hierarchy_columns]
if(is.null(value_column) && add_root){
setcolorder(DT, c("root", colNamesDF))
} else if(!is.null(value_column) && !add_root) {
setnames(DT, value_column, "values", skip_absent=TRUE)
setcolorder(DT, c(setdiff(colNamesDF, value_column), "values"))
} else if(!is.null(value_column) && add_root) {
setnames(DT, value_column, "values", skip_absent=TRUE)
setcolorder(DT, c("root", setdiff(colNamesDF, value_column), "values"))
}
hierarchyList <- list()
for(i in seq_along(hierarchy_columns)){
current_columns <- colNamesDT[1:i]
if(is.null(value_column)){
currentDT <- unique(DT[, ..current_columns][, values := .N, by = current_columns], by = current_columns)
} else {
currentDT <- DT[, lapply(.SD, mean, na.rm = TRUE), by=current_columns, .SDcols = "values"]
}
setnames(currentDT, length(current_columns), "labels")
hierarchyList[[i]] <- currentDT
}
hierarchyDT <- rbindlist(hierarchyList, use.names = TRUE, fill = TRUE)
parent_columns <- setdiff(names(hierarchyDT), c("labels", "values", value_column))
hierarchyDT[, parents := apply(.SD, 1, function(x){fifelse(all(is.na(x)), yes = NA_character_, no = paste(x[!is.na(x)], sep = ":", collapse = " - "))}), .SDcols = parent_columns]
hierarchyDT[, ids := apply(.SD, 1, function(x){paste(x[!is.na(x)], collapse = " - ")}), .SDcols = c("parents", "labels")]
hierarchyDT[, c(parent_columns) := NULL]
return(hierarchyDT)
}
# Valueboxes:
sanejament_basic <- sum(dades_subset_sanejament$Poblacio_milers*dades_subset_sanejament$Sanejament_basic/100, na.rm=T) / sum(dades_subset_sanejament$Poblacio_milers, na.rm=T) * 100
sanejament_basic <- paste(round(sanejament_basic,2), "%")
```
<!-- Sanejament -->
<!-- ======================================================================= -->
Inputs {.sidebar}
-----------------------------------------------------------------------
Segons dades de l'Organització Mundial de la Slaut, **el sanejament de les aigües residuals** deficient és la causa principal d'unes 432.000 morts anuals i és un factor important en diverses malalties, com ara cucs intestinals, esquistosomiasi i tracoma. Un sanejament deficient també contribueix a la desnutrició.
El **2020**, el 77% de la població mundial va utilitzar almenys un servei de sanejament bàsic; el 23% restant no va tenir accés al sanejament bàsic.
A continuació es pot consultar per país l'**estat del sanejament de les aigües residuals**:
```{r filters}
#FILTRE:
filter_select(
id = "id_Pais",
label = "Llistat de Països",
sharedData = sd,
group = ~Pais,
multiple = T
)
```
El següent *slider* permet veure com evoluciona l’accés a un sanejament segur en funció del **Producte Interior Brut** de cada país.
```{r}
filter_slider(
id = "Any",
label = "Any de consulta",
sharedData = sd_hist,
~Any)
```
Column {data-width=600}
-------------------------------------
### de la població mundial té al menys sanejament bàsic garantit {.value-box}
```{r}
valueBox(sanejament_basic, icon = "fa-hand-holding-droplet")
```
### Percentatge de població amb accés a Sanejament bàsic al 2020
```{r}
leaflet(sd) %>%
setView(lng = 0, lat = 30, zoom = 2) %>%
addProviderTiles(providers$CartoDB.DarkMatter) %>%
addPolygons(fillColor = ~pal(Sanejament_basic), fillOpacity = 0.75, color="grey", weight = 1,
popup = ~paste(
"<a style='color:#000000'> País: </a>", "<b style='color:#000000'>", Pais, "</b><br>",
"<a style='color:#000000'> Població sanejada: </a>", "<b style='color:#000000'>", Sanejament_basic, "%", "</b><br>")) %>%
addLegend("bottomleft", title = "% de població", labFormat = labelFormat(suffix = "%"), pal = pal, values = ~Sanejament_basic, opacity = 0.75)
```
Column {data-width=400}
-------------------------------------
### % de població assistida per sistemes de tractament gestionats amb seguretat
```{r}
sunburstDF <- as.sunburstDF(DF_sunburst, value_column = "valor", add_root = TRUE)
plot_ly(data = sunburstDF, ids = ~ids, labels= ~labels, parents = ~parents, values= ~values, type='sunburst', branchvalues = 'relative') %>%
layout(
plot_bgcolor = "rgba(0,0,0,0)",
paper_bgcolor = "rgba(0,0,0,0)")
```
### Evolució de l'accés al sanejament bàsic
```{r fig.height=6}
t <- list(
family = "Courier New",
size = 12,
color = "white")
colors <- c('red', 'darkgreen', '#ff850b', '#1972A4', '#965F8A')
fig <- plot_ly(data = sd_hist, x = ~GDP, y = ~Sanejament_basic, mode = 'markers', type='scatter', color= ~continent, size= ~Poblacio_milers, colors= colors,
marker = list(opacity = 0.8, line = list(width = 0.2, color = '#FFFFFF')),
text = ~paste('Country:', Pais, '<br>% població amb sanejament bàsic:', Sanejament_basic, '<br>Any:', Any,
'<br>Població:', Poblacio_milers)
) %>%
layout(
showlegend = FALSE,
plot_bgcolor = "rgba(0,0,0,0)",
paper_bgcolor = "rgba(0,0,0,0)",
xaxis = list(title = list(text = 'Producte Interior Brut (PIB) [$]', font = t), type = "log"),
yaxis = list(title = list(text = '% de població amb accés a sanejament bàsic', font=t)),
font=t
)
fig
# sizemode = 'diameter',
```